package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.medical.entity.FamilyArchives;
import com.groot.medical.entity.PersonalFiles;

public class PersonalFileDaoImpl implements PersonalFileDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	private java.sql.Date fomart(java.util.Date data){
		return new java.sql.Date(data.getTime());
	}
	@Override
	public int addHolderPersonInfo(PersonalFiles person) {
		int i=0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_personnel_file(family_code,farmingcard_code,medicalcard_code,name,holder_relation,idcard_number,sex,health,nation,cultrue_degree,birthday,person_property,is_contryside,occupation,work_address,phone_number,often_address,is_marry,family_in_code)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setString(1,person.getFamilyCode());
			ps.setString(2, person.getFarmingcardCode());
			ps.setString(3, person.getMedicalcardCode());
			ps.setString(4,person.getName());
			ps.setString(5, person.getHolderRelation());
			ps.setString(6, person.getIdcardNumber());
			ps.setString(7,person.getSex());
			ps.setString(8, person.getHealth());
			ps.setString(9, person.getNation());
			ps.setString(10,person.getCultrueDegree());
			ps.setDate(11,fomart( person.getBirthday()));
			ps.setString(12,person.getPersonProperty());
			ps.setString(13,person.getIsContryside());
			ps.setString(14,person.getOccupation());
			ps.setString(15,person.getWorkAddress());
			ps.setString(16,person.getPhoneNumber());
			ps.setString(17,person.getOftenAddress());
			ps.setString(18,person.getIsMarry());
			ps.setString(19,person.getFamilyInCode());
			i=ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		return i;
		
	}
	@Override
	public int getAllPersonCount() {
		int i =0;
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
		conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
		String sql="SELECT count(1) AS id FROM t_personnel_file";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		if(rs.next()){
			i=rs.getInt("id");
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return i;
	}
	@Override
	public List findPersonByPage(int startIndex, int pageSize) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_personnel_file limit ?,?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
		
			List<PersonalFiles> persons = new ArrayList<>();
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
			
				PersonalFiles person = new PersonalFiles();
				
				person.setFamilyCode(rs.getString(1));
				person.setFarmingcardCode(rs.getString(2));
				person.setMedicalcardCode(rs.getString(3));
				person.setName(rs.getString(4));
				person.setHolderRelation(rs.getString(5));
				person.setIdcardNumber(rs.getString(6));
				person.setSex(rs.getString(7));
				person.setHealth(rs.getString(8));
				person.setNation(rs.getString(9));
				person.setCultrueDegree(rs.getString(10));
				person.setBirthday(rs.getDate(11));
				person.setPersonProperty(rs.getString(12));
				person.setIsContryside(rs.getString(13));
				person.setOccupation(rs.getString(14));
				person.setWorkAddress(rs.getString(15));
				person.setPhoneNumber(rs.getString(16));
				person.setOftenAddress(rs.getString(17));
				person.setIsMarry(rs.getString(18));
				person.setFamilyInCode(rs.getString(19));
				
				
				persons.add(person);
				
			}
			
			return persons;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public PersonalFiles findPersonInfoById(String idcardNumber) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_personnel_file where idcard_number = ?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, idcardNumber);
			rs = ps.executeQuery();
		
			PersonalFiles person = null;
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
			
				person= new PersonalFiles();
				
				person.setFamilyCode(rs.getString(1));
				person.setFarmingcardCode(rs.getString(2));
				person.setMedicalcardCode(rs.getString(3));
				person.setName(rs.getString(4));
				person.setHolderRelation(rs.getString(5));
				person.setIdcardNumber(rs.getString(6));
				person.setSex(rs.getString(7));
				person.setHealth(rs.getString(8));
				person.setNation(rs.getString(9));
				person.setCultrueDegree(rs.getString(10));
				person.setBirthday(rs.getDate(11));
				person.setPersonProperty(rs.getString(12));
				person.setIsContryside(rs.getString(13));
				person.setOccupation(rs.getString(14));
				person.setWorkAddress(rs.getString(15));
				person.setPhoneNumber(rs.getString(16));
				person.setOftenAddress(rs.getString(17));
				person.setIsMarry(rs.getString(18));
				person.setFamilyInCode(rs.getString(19));
			}
			
			return person;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public String autoCreateFamilyInCode(String familyCode) {
		/*int grade=Integer.parseInt(parea.getAreaLevel())+1;*/
		String sql="SELECT MAX(family_in_code) FROM t_personnel_file where  family_code='"+familyCode+"'" ;
	
		try{
			conn=MedicalDBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			String maxcode="";
			Integer number=1;
			if(rs.next()){
			   maxcode=rs.getString(1);
			}		
			if(maxcode!=null){
				
			  
			  number=Integer.parseInt(maxcode);
			  ++number;
			  //使用0补足位数
			  String no=String.format("%02d", number);
			  maxcode=no;
			}else{
				String no=String.format("%02d", number);
				
			}
			return maxcode;
		}catch(SQLException e){
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public void delPersonInfo(String familyCode) {
		
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="delete  from t_personnel_file where family_code=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, familyCode);
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
		
	}
	@Override
	public void editPersonInfo(PersonalFiles person) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="update t_personnel_file set family_code=?,farmingcard_code=?,medicalcard_code=?,name=?,holder_relation=?,sex=?,health=?,nation=?,cultrue_degree=?,birthday=?,person_property=?,is_contryside=?,occupation=?,work_address=?,phone_number=?,often_address=?,is_marry=?,family_in_code=? where idcard_number=?";
			ps=conn.prepareStatement(sql);
			int index=1;
			ps.setString(index++,person.getFamilyCode());
			ps.setString(index++, person.getFarmingcardCode());
			ps.setString(index++, person.getMedicalcardCode());
			ps.setString(index++,person.getName());
			ps.setString(index++, person.getHolderRelation());
			
			ps.setString(index++,person.getSex());
			ps.setString(index++, person.getHealth());
			ps.setString(index++, person.getNation());
			ps.setString(index++,person.getCultrueDegree());
			ps.setDate(index++,fomart( person.getBirthday()));
			ps.setString(index++,person.getPersonProperty());
			ps.setString(index++,person.getIsContryside());
			ps.setString(index++,person.getOccupation());
			ps.setString(index++,person.getWorkAddress());
			ps.setString(index++,person.getPhoneNumber());
			ps.setString(index++,person.getOftenAddress());
			ps.setString(index++,person.getIsMarry());
			ps.setString(index++,person.getFamilyInCode());
			ps.setString(index++, person.getIdcardNumber());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}
	@Override
	public int deletePersonInfo(String idCardNum) {
		int i=0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="delete from t_personnel_file where idcard_number=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, idCardNum);
			i=ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		return i;
	}

}
